import 'dart:convert';

import 'package:account/bean/group.dart';
import 'package:sqflite/sqflite.dart';

import 'utils.dart';

class MyData {
  static Database? _db;
  static final String _baseName = "my_db.db";
  static final String _tableNameGroup = "my_group";
  static final String _tableName = "account";

  static init() async {
    _db = await openDatabase(_baseName);
    print("路径：" + await getDatabasesPath());
    //创建分组表
    await _db!.execute(
        "create table if not exists $_tableNameGroup(id integer primary key autoincrement,name text,info text)");
    //创建用户表
    await _db!.execute(
        "create table if not exists $_tableName(id integer primary key autoincrement,g_id integer,name text,list text,time integer)");
    //如果没有分组则创建一个name为未命名的分组
    await _createGroupIfEmpty();
  }

  ///如果没有分组则创建一个name为未命名的分组
  static _createGroupIfEmpty() async {
    final String sqlSelect = "select * from $_tableNameGroup";
    List<Map<String, Object?>> list = await _db!.rawQuery(sqlSelect);
    if (list.length > 0) return;
    final String sqlInsert = "insert into $_tableNameGroup(name) values('未命名')";
    await _db!.execute(sqlInsert);
  }

  ///保存账号并修改相关组的内容
  ///
  /// [position] 对应[QUtils.listGroup]的下标
  static Future<bool> saveAccountWithGroup(
      Account account, int position) async {
    //获取有没有相同的账号
    final String sqlSelect =
        "select * from  $_tableName where name = \'${account.list![0].value}\'";
    List<Map<String, Object?>> list = await _db!.rawQuery(sqlSelect);
    if (list.length > 0) return false;
    //当前没有此账号，添加到数据库
    String name = account.list![0].value!; //名称
    String json = jsonEncode(account); //json格式的实体类
    final String sqlInsert =
        "insert into $_tableName(name,list,time) values('$name','$json',${account.time})";
    await _db!.execute(sqlInsert);
    //获取account的ID
    final String sqlId = "select * from $_tableName where name = '$name'";
    List<Map<String, Object?>> idList = await _db!.rawQuery(sqlId);
    account.id = idList[0]['id'] as int;
    //修改相关组的内容
    if (QUtils.listGroup[position].list == null)
      QUtils.listGroup[position].list = [];
    QUtils.listGroup[position].list!.add(account);
    String jsonGroup = jsonEncode(QUtils.listGroup[position]);
    final String updateGroup =
        "update $_tableNameGroup set info = '$jsonGroup' where id = ${account.gId} ";
    await _db!.execute(updateGroup);
    return true;
  }

  ///保存一个账号
  ///如果账号已经存在返回false，否则添加到数据库并返回true
  ///
  /// 使用[saveAccountWithGroup]
  @Deprecated('因结构发生变化，此方法被弃用')
  static Future<bool> saveAccount(Account account) async {
    //获取有没有相同的账号
    final String sqlSelect =
        "select * from  $_tableName where name = \'${account.list![0].value}\'";
    List<Map<String, Object?>> list = await _db!.rawQuery(sqlSelect);
    if (list.length > 0) return false;
    //当前没有此账号，添加到数据库
    String name = account.list![0].value!; //名称

    String json = jsonEncode(account); //json格式的实体类
    final String sqlInsert =
        "insert into $_tableName(name,list,time) values('$name','$json',${account.time})";
    await _db!.execute(sqlInsert);
    return true;
  }

  ///保存一个分组到数据库
  static saveGroup(String text) async {
    final String sqlGroup =
        "insert into $_tableNameGroup(name,info) values('$text','${jsonEncode(Group(name: text))}')";
    await _db!.execute(sqlGroup);
  }

  ///获取所有缓存的账号内容
  ///此方法已被弃用，请使用[getAllGroupAndAccount]
  @Deprecated('因结构发生改变，此方法被弃用')
  static Future<List<Account>?> getAllAccount() async {
    final String sqlSelect = "select * from $_tableName";
    List<Map<String, Object?>> tempList = await _db!.rawQuery(sqlSelect);
    List<Account> list = [];
    //获取每一行的内容 json直接代表一个account
    for (Map<String, Object?> map in tempList) {
      //查看map中的所有内容代码
      for (String key in map.keys) {
        print("key:$key,,,value:${map[key]}");
      }
      Account account = Account.fromJson(jsonDecode(map['list'] as String));
      account.time = map['time'] as int;
      account.id = map['id'] as int;
      list.add(account);
    }
    return list;
  }

  ///获取所有的组和相关的账号内容
  static getAllGroupAndAccount() async {
    final String sqlSelect = "select * from $_tableNameGroup";
    List<Map<String, Object?>> sqlList = await _db!.rawQuery(sqlSelect);
    List<Group> list = [];
    //根据分组id获取相关账号，并存到list中
    for (Map map in sqlList) {
      String? info = map['info'];
      Group group;
      if (info == null) {
        group = Group();
      } else {
        group = Group.fromJson(jsonDecode(info));
      }
      group.id = map['id'];
      group.name = map['name'];
      list.add(group);
    }
    QUtils.listGroup = list;
  }

  ///更新账号信息并更新相关组的内容
  ///
  /// [position] 对应[QUtils.listGroup]的下标
  static Future<int> updateAccountWithGroup(
      Account? account, int position) async {
    //查询账号是否存在
    final String sqlSelectId =
        "select * from $_tableName where id = ${account!.id}";
    List<Map<String, Object?>> tempList = await _db!.rawQuery(sqlSelectId);
    if (tempList.length == 0) return 1;
    //查询当前是否已经有了相同的名称，有则视为不合法
    final String sqlSelectName =
        "select * from $_tableName where name = '${account.list![0].value}' and id != ${account.id}";
    List<Map<String, Object?>> tempListName =
        await _db!.rawQuery(sqlSelectName);
    if (tempListName.length > 0) return 2;
    //合法内容，更新显示
    String accountJson = jsonEncode(account);
    final String sqlUpdate =
        "update $_tableName set name = '${account.list![0].value}', list = '$accountJson' where id = ${account.id}";
    await _db!.execute(sqlUpdate);
    //更新相关组的内容
    //修改组的账号内容
    for (int i = 0; i < QUtils.listGroup[position].list!.length; i++) {
      if (QUtils.listGroup[position].list![i].id == account.id) {
        QUtils.listGroup[position].list![i] = account;
        break;
      }
    }
    String groupStr = jsonEncode(QUtils.listGroup[position]);
    final String updateGroup =
        "update $_tableNameGroup set info = '$groupStr' where id = ${account.gId}";
    await _db!.execute(updateGroup);
    return 0;
  }

  ///更新某个账号的信息
  ///0.成功 ，1.账号不存在，2.已存在相同的key
  @Deprecated('因结构发生改变，此方法过时')
  static Future<int> update(Account? account) async {
    //查询账号是否存在
    final String sqlSelectId =
        "select * from $_tableName where id = ${account!.id}";
    List<Map<String, Object?>> tempList = await _db!.rawQuery(sqlSelectId);
    if (tempList.length == 0) return 1;
    //查询当前是否已经有了相同的名称，有则视为不合法
    final String sqlSelectName =
        "select * from $_tableName where name = '${account.list![0].value}' and id != ${account.id}";
    List<Map<String, Object?>> tempListName =
        await _db!.rawQuery(sqlSelectName);
    if (tempListName.length > 0) return 2;
    //合法内容，更新显示
    String accountJson = jsonEncode(account);
    final String sqlUpdate =
        "update $_tableName set name = '${account.list![0].value}', list = '$accountJson' where id = ${account.id}";
    print("update:$sqlUpdate");
    await _db!.execute(sqlUpdate);
    return 0;
  }

  ///删除账号
  ///
  /// [index]和[position]对应[QUtils.listGroup]及其list的下标
  static deleteAccount(int index, int position) async {
    //删除账号列表中的数据
    final String sqlDeleteAccount =
        "delete from $_tableName where id = ${QUtils.listGroup[index].list![position].id}";
    await _db!.execute(sqlDeleteAccount);
    //更新相关组的内容
    QUtils.listGroup[index].list!.removeAt(position);
    String groupJson = jsonEncode(QUtils.listGroup[index]);
    final String sqlUpdateGroup =
        "update $_tableNameGroup set info = '$groupJson'";
    await _db!.execute(sqlUpdateGroup);
    //重新获取组和账号的内容
    await getAllGroupAndAccount();
  }

  ///删除某个分组及其相关的所有账号
  ///
  /// [index] 代表 [QUtils.listGroup]的下标，当前操作的是哪个分组
  static deleteGroup(int index) async {
    //删除分组的内容
    List<Account>? list = QUtils.listGroup[index].list;
    if (list != null) {
      for (Account account in list) {
        final String sqlDeleteAccount =
            "delete from $_tableName where id = ${account.id}";
        await _db!.execute(sqlDeleteAccount);
      }
    }
    //删除分组内容
    final String sqlDeleteGroup =
        "delete from $_tableNameGroup where id = ${QUtils.listGroup[index].id}";
    await _db!.execute(sqlDeleteGroup);
  }
}
